<?
/***********************************
        * Sistema de Administracion Hotelera
        * Archivo:              EXCEL.php
        * Proposito:    Exporta la relacion de visitantes a un archivo excel
        * Creado por:   Isaac Esqueda
        * Creado:               10/05/2008
        ************************************/
?>
<?
require_once('../../Common/secure.php');
validateSessionAlive();
	validateAccess(array("2"), $_SESSION['UserRol']);
require_once('../../Common/DAO.php');
$dao=new DAO;
?>
<?php
//Parameters recieved from the first date range
	$anioD = $_POST['anioD'];
	$mesD = $_POST['mesD'];
	$diaD = $_POST['diaD'];
	
	
//Parameters recieved form the second date range
	$anioD2 = $_POST['anioD2'];
	$mesD2 = $_POST['mesD2'];
	$diaD2 = $_POST['diaD2'];
	
	
	//Execute the query
$dia = 1;
$query = "SELECT DATE(tbl_Bill.BillDateTime) AS Fecha, sum(tbl_bill.NumPersonsNationals)as Nacionales,
	sum(tbl_bill.NumPersonsForeigners) AS Extranjeros,
	sum(tbl_bill.NumPersonsNationals + tbl_bill.NumPersonsForeigners) AS Total FROM tbl_bill
	where BillDateTime >= '$anioD-$mesD-$diaD' AND BillDateTime < ADDDATE('$anioD2-$mesD2-$diaD2',$dia) AND NOT (IsReservation=1 AND IsCancelled=1)
	group by DATE(tbl_bill.BillDateTime) WITH ROLLUP";

	
//Set $result1 as the query	

$result1 = $dao->run_query($query);



// Functions for export to excel.
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}

function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
header("Pragma: public");
header ("Content-Type: text/html; charset=ISO-8859-9");  
header("Expires: 0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=SECTUR.xls ");
header("Content-Transfer-Encoding: binary ");

xlsBOF();

/*
Make a top line on your excel sheet at line 1 (starting at 0).
The first number is the row number and the second number is the column, both are start at '0'
*/

xlsWriteLabel(0,0,"Relacion de Visitantes.");

// Make column labels. (at line 3)

xlsWriteLabel(2,0,"Fecha");
xlsWriteLabel(2,1,"Nacionales");
xlsWriteLabel(2,2,"Extranjeros");
xlsWriteLabel(2,3,"Total");
$xlsRow = 3;

// Put data records from mysql by while loop.
//while($row=mysql_fetch_array($result1)){

 while($row=mysql_fetch_assoc($result1)){
xlsWriteLabel($xlsRow,0,$row["Fecha"]);
xlsWriteNumber($xlsRow,1,$row["Nacionales"]);
xlsWriteNumber($xlsRow,2,$row["Extranjeros"]);
xlsWriteNumber($xlsRow,3,$row["Total"]);


$xlsRow++;
}
xlsEOF();
exit();
?>